﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PRC_TB_USER_BY_PROJECTS_SE')
	BEGIN
		DROP  Procedure  dbo.PRC_TB_USER_BY_PROJECTS_SE
	END

GO

CREATE PROCEDURE [dbo].[PRC_TB_USER_BY_PROJECTS_SE] 
(
    @PROJECT_LIST xml = null
)
AS
    SELECT 
            a.TU_ID, 
            a.TU_NAME, 
            a.TU_LOGIN, 
            a.TU_PASSWORD,
            a.TU_EMAIL,
            a.TU_PROFILER_ID, 
            a.TU_GROUP_ID,
            b.TG_ID,
            b.TG_NAME,
            b.TG_IDENTIFICATION,
            b.TG_DESCRIPTION,
            c.TP_ID,
            c.TP_DESCRIPTION
	    FROM TB_USER a
            INNER JOIN TB_GROUPS b
                on a.TU_GROUP_ID = b.TG_ID
            INNER JOIN TB_PROFILERS c
                on a.TU_PROFILER_ID = c.TP_ID
	    WHERE (@PROJECT_LIST is null or 
                a.TU_ID in (select 
                            distinct d.TUP_USER_ID 
                            from TB_USER_PROJECTS d 
                            where d.TUP_PROJECT in (select RTRIM(LTRIM(ParamValues.project_id.value('.','VARCHAR(150)'))) from @PROJECT_LIST.nodes('/ProjectSearch/Projects/project_id') as ParamValues(project_id))
                           )
              )
        ORDER BY a.TU_NAME


GO

GRANT EXEC ON dbo.PRC_TB_USER_BY_PROJECTS_SE TO PUBLIC

GO

